create or replace function a()return int as  declare  	result int;begin 	SELECT "MINUTE"(CURTIMESTAMP()) into result;return result;end;language 'ploscar';
create or replace procedure pro_test_update2(tn varchar(10),before_count out int,after_count out int,normal_table varchar(10)) as declare 	part_name varchar(100)='';part_id int;begin 	 	 select max(oid) into part_id from v_sys_tab_partitions where RELNAME=tn;part_name='SYS_P'||part_id;execute immediate 'select count(*) from ' || normal_table into before_count;execute immediate 'alter table '||tn||' exchange partition '||part_name ||' with table '||normal_table||' update indexes(ind3(partition ind_p1 tablespace ts1))';execute immediate 'select count(*) from ' || normal_table into after_count;end;language 'ploscar';
create or replace package test_33 as 	procedure insert_table  ;end test_33;
create or replace procedure whileloop(i inout int,j inout int)   as DECLARE     numberA int;num int;boolh boolean;BEGIN         numberA:= j;while true LOOP 	  exit when NOT whileloopED(numberA);if numberA<0  	    then numberA = 20;i=i+1;else numberA:=numberA-1;i:=i+1;end if;END LOOP;return ;END;
create or replace procedure dyn_exec(a1 int) as v1 int := 1;v2 varchar(20) := 'shangbo';v3 date := '2010-03-23';string varchar(20);begin 	execute immediate 'insert into mytable values(:1,:2,:3) returning b into :retval' 	using in v1,in v2,in v3,in out string;raise notice '% % % return %',v1,v2,v3,string;end;
create or replace procedure test() as  curs1 REFCURSOR;emp_rec employees%ROWTYPE;BEGIN    OPEN curs1 FOR SELECT * FROM employees WHERE employee_id < 120;FETCH curs1 INTO emp_rec;if  curs1%FOUND then     DBMS_OUTPUT.PUT_LINE         ('Name = ' || emp_rec.first_name || ' ' || emp_rec.last_name);end if;CLOSE curs1;END;
create or replace procedure a() as  declare  	result float4;begin 	select ASCII('a') into result;dbms_output.put_line(result);end;language 'ploscar';
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE NESTEDTABLE IS TABLE OF varchar2(10);MYNESTED NESTEDTABLE := NESTEDTABLE('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10');I INT;BEGIN 	DBMS_OUTPUT.PUT_LINE('Count is : '||MYNESTED.COUNT);MYNESTED.DELETE;DBMS_OUTPUT.PUT_LINE('Count is : '||MYNESTED.COUNT);MYNESTED.TRIM(5);DBMS_OUTPUT.PUT_LINE('Count is : '||MYNESTED.COUNT);i	:= MYNESTED.FIRST;WHILE i IS NOT NULL LOOP 		dbms_output.put_line('Data of '|| i || ' is '||MYNESTED(i));i	:= MYNESTED.NEXT(i);END LOOP;END;
create or replace procedure a()  as  declare  	result int;id text:='SEQ';begin 	CREATE SEQUENCE SEQ;SELECT NEXTVAL(id) into result;drop SEQUENCE SEQ;dbms_output.put_line(result);end;language 'ploscar';
CREATE OR REPLACE PROCEDURE P1 AS 	TYPE COLLECTION IS TABLE OF T1%ROWTYPE INDEX BY PLS_INTEGER;TAB COLLECTION;V1	T1.ID%TYPE;V2	T1.NUM%TYPE;V3	T1.NAME%TYPE;ITERATIONS CONSTANT PLS_INTEGER := 5;K INT;BEGIN 	EXECUTE IMMEDIATE 'UPDATE T1 SET NUM = 100,NAME = ''NEW'' RETURNING ID,NUM,NAME INTO :1' 	RETURNING BULK COLLECT INTO TAB;K := TAB.FIRST;DBMS_OUTPUT.PUT_LINE('== TAB ==');DBMS_OUTPUT.PUT_LINE(TAB.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('== INDEX ' || K ||' == ');V1 := TAB(K).ID;V2 := TAB(K).NUM;V3 := TAB(K).NAME;DBMS_OUTPUT.PUT_LINE(' ID   ' || V1);DBMS_OUTPUT.PUT_LINE(' NUM  ' || V2);DBMS_OUTPUT.PUT_LINE(' NAME ' || V3);K	:= TAB.NEXT(K);END LOOP;END;
create or replace function Test_proc01(id int8)return int8 as  declare  	result int8;begin 	select INT8SMALLER(id,80::int8) into result;return result;end;language 'ploscar';
create or replace procedure p_11891(n int) as declare 	i int = 1;begin 	while i <= n loop 				insert into t_11891_1 values(i, repeat('a', 100));insert into t_11891_2 values(i, repeat('a', 100));i = i+1;end loop;end;
create or replace procedure test()   is  declare 	type varchar1 is record(a1 int,a2 varchar(10));test varchar1;test2 varchar1;begin 	test2.a1:=10;test2.a2:='sss';test:=test2;DBMS_OUTPUT.PUT_LINE('k1='||test.a1);DBMS_OUTPUT.PUT_LINE('k2='||test.a2);end;
create or replace function p16() return int as declare   r sysdba.test_int%rowtype;begin   select * into r from test_int;return r.j;end;language 'ploscar';
create or replace function a()return float as  declare  	result float;begin 	select max(id) into result from a;return result;end;language 'ploscar';
CREATE TRIGGER tria after insert     on aaaa for each row  begin       	 	insert into bbbb values (0);EXCEPTION         WHEN others THEN             RAISE NOTICE 'caught foreign key error';end;
create or replace procedure forexample(a inout int) as declare i int;begin   for i in 1..100 loop     a:=a+1;end loop;return ;end;language 'ploscar';
create or replace function g return sys_refcursor is     a sys_refcursor;begin     open a for execute 'select * from test order by a';return a;end;
create or replace procedure get(a in int )  as begin       null;end;language 'ploscar';
create or replace procedure a() as declare    b varchar(12);c varchar(12);begin   null;end;
CREATE OR REPLACE TRIGGER TRG1 AFTER INSERT OR UPDATE OR DELETE ON T1 FOR EACH ROW DECLARE V1 INT;BEGIN 		P1(:NEW.A);END;
create or replace function testFunc10(xmlData varchar2, looptime int) return varchar2 as     type parsers is table of dbms_xmlparser.parser index by pls_integer;type docs is table of dbms_xmldom.domdocument index by pls_integer;v_parser          dbms_xmlparser.Parser;v_xmldoc          dbms_xmldom.DOMDocument;v_rootnode        dbms_xmldom.DOMNode;v_node            dbms_xmldom.DOMNode;v_nodelist        dbms_xmldom.DOMNodeList;v_parser1         dbms_xmlparser.Parser;v_xmldoc1         dbms_xmldom.DOMDocument;v_rootnode1       dbms_xmldom.DOMNode;v_node1           dbms_xmldom.DOMNode;v_nodelist1       dbms_xmldom.DOMNodeList;v_nl              INT;v_element_value   VARCHAR2(200);v_attribute_value VARCHAR2(200);v_parsers         parsers;v_xmldocs         docs;res               varchar2(100);BEGIN            IF xmlData IS NULL THEN       RETURN NULL;END IF;for i in 1 .. looptime loop                      v_parser := dbms_xmlparser.newParser;dbms_xmlparser.parseBuffer(v_parser, xmlData);v_xmldoc := dbms_xmlparser.getDocument(v_parser);v_rootnode := dbms_xmldom.getFirstChild(dbms_xmldom.makeNode(v_xmldoc));v_nodelist := dbms_xslprocessor.selectNodes(v_rootnode, 'c');v_parsers(i) := v_parser;v_xmldocs(i) := v_xmldoc;end loop;for i in 1 .. looptime loop         dbms_xmlparser.freeparser(v_parsers(i));dbms_xmldom.freedocument(v_xmldocs(i));end loop;return 'OK';exception when others then         res := XML_CLEAN_PKG_ARRAY();return 'exception';END;
create or replace  function f(i1 int ) return varchar2 as begin     raise notice '%',i1;return 'aaaaaaaaaaaaaaaaaaaaaa';end;
CREATE PROCEDURE blockproc() AS  DECLARE    foo INTEGER := 40;BEGIN    RAISE NOTICE 'foo before sub-block IS %',foo;foo := 50;DECLARE       foo INTEGER := 60;BEGIN       RAISE NOTICE 'foo in sub-block IS %',foo;END;RAISE NOTICE 'foo after sub-block IS %',foo;END;language 'ploscar';
CREATE OR REPLACE TRIGGER TRI1 BEFORE INSERT OR UPDATE OR DELETE  ON T1 FOR EACH ROW BEGIN     IF UPDATING(' ' || 'a' || '') THEN         DBMS_OUTPUT.PUT_LINE('UPDATE COLUMN A');END IF;END;
create or replace function printWithOutOid(s varchar2) return varchar2 is     temp varchar2;temp1 varchar2;result varchar2;i int;begin     temp := s;for i in 1 .. 3 loop         temp := substr(temp, instr(temp, chr(10)) + 1);end loop;result := '';loop         temp1 := substr(temp, 9, instr(temp, chr(10)) - 8);temp1 := trim(temp1);result := result || temp1;temp := substr(temp, instr(temp, chr(10)) + 1);exit when (temp is null or temp = '');end loop;return result;end;
create or replace function pbooleanExe(_id int,fb boolean) return boolean as declare boolvar boolean := false;begin         if pbool(2,true) then        boolvar = true;end if;return boolvar;end;language 'ploscar';
create or replace procedure a()  as  declare  	result text;begin 	select TRANSLATE('abcdefg','cd','xy') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure a()  as  declare  	result int;begin 	select POSITION('as' in b) into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure ppp2 is begin     ppp1;exception when others then         insert into test values(_format_error_backtrace);end;
create or replace procedure p7(k int)   as declare   rename k to i;begin   execute 'update t_int set i=3 where i = ' || i;return;end;language ploscar;
create or replace package Calculator  as   version constant varchar(50):='version 1.1.1.1';company constant varchar(50):='�������';procedure produ_desc;procedure display(first int ,second int);function divide(first int,second int)     return float;divzero exception;end Calculator;
create or replace procedure a()  as  declare  	result int;begin 	SELECT LOCATE('abc',null,1) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select POWER(22.3,10.5) into result;return result;end;language 'ploscar';
create or replace procedure p1()   as declare i int;begin   for i in 1..5 loop     raise notice 'i = %', i;end loop;return;end;
CREATE or replace PROCEDURE reffunc2() AS 	DECLARE     	ref refcursor;BEGIN 		OPEN ref FOR SELECT col FROM test;END;language 'ploscar';
create or replace package test as 	procedure pro1 ;function fun1 return varchar2 ;end;
create or replace procedure p_4(i int not null,j int not null default 100)   is  declare        sum int;begin 	sum = i+j;return;end;language 'ploscar';
create trigger a_column1 BEFORE update of id ON a FOR EACH ROW  Begin 	RAISE NOTICE 'column_trigger() called: action = %, when = %, level = %', TG_OP, TG_WHEN, TG_LEVEL;insert into result values(0, TG_OP, TG_WHEN, TG_LEVEL);end;
create trigger a_column20 AFTER update of id ON a FOR EACH ROW  Begin 	RAISE NOTICE 'column_trigger() called: action = %, when = %, level = %', TG_OP, TG_WHEN, TG_LEVEL;insert into result values(0, TG_OP, TG_WHEN, TG_LEVEL);end;
create trigger t1 before update on lz1 for each row  begin 	execute immediate 'insert into lz2 values('||new.id+1||','''||new.name||''')';end;
CREATE OR REPLACE PROCEDURE P1 AS 	TYPE REC IS RECORD( 		FIELD1 T1.ID%TYPE DEFAULT 10, 		FIELD2 T1.NUM%TYPE := 100, 		FIELD3 T1.NAME%TYPE := 'OLD');TYPE IDTAB IS TABLE OF REC INDEX BY PLS_INTEGER;R1 REC;R2 REC;IDS IDTAB;K INT;V1	T1.ID%TYPE;V2	T1.NUM%TYPE;V3	T1.NAME%TYPE;BEGIN 	IDS(1) := R1;IDS(2) := R2;IDS(1).FIELD1 := 1;IDS(1).FIELD2 := 11;IDS(1).FIELD3 := 'NEW';R1.FIELD1 := 2;R1.FIELD2 := 22;R1.FIELD3 := 'NEW';R2 := R1;IDS(2) := R2;K := IDS.FIRST;DBMS_OUTPUT.PUT_LINE('== IDS ==');DBMS_OUTPUT.PUT_LINE(IDS.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('== INDEX ' || K ||' == ');V1 := IDS(K).FIELD1;V2 := IDS(K).FIELD2;V3 := IDS(K).FIELD3;DBMS_OUTPUT.PUT_LINE(' FIELD1 : ' || V1);DBMS_OUTPUT.PUT_LINE(' FIELD2 : ' || V2);DBMS_OUTPUT.PUT_LINE(' FIELD3 : ' || V3);K	:= IDS.NEXT(K);END LOOP;END;
create or replace procedure a()  as  declare  	result text;begin 	select TO_CHAR(c,'000.00') into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure a()  as  declare  	result int;id varchar(10):='SEQ';begin 	CREATE SEQUENCE SEQ;select nextval(id) into result ;SELECT CURRVAL(id) into result;drop SEQUENCE SEQ;dbms_output.put_line(result);end;language 'ploscar';
create   or   replace   procedure   pro ( 	id in number, 	salary in number, 	action varchar2(20))  as  declare 	sql_str varchar2(500);begin 	sql_str:='insert into employees values(:1,:2)';execute immediate sql_str using id,salary,action;end;
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE COL_TYP IS TABLE OF INT;MyTab COL_TYP := COL_TYP(1,2,3,4,5);K INT;BEGIN 	K := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE('Count is : '||K);MyTab.DELETE(1);K := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE('Count is : '||K);K := MyTab(1);DBMS_OUTPUT.PUT_LINE('MyTab(1) is : '||K);END;
create or replace procedure forexample(a inout int)   as declare i int;j int;begin   for i in reverse  100..1 loop     a:=a+1;for j in reverse  100..1 loop       a:=a+1;end loop;end loop;return ;end;language 'ploscar';
CREATE OR REPLACE procedure TEST(r1 out text) is	 	e1 exception;BEGIN 	raise e1;exception when e1 then 	Dbms_output.put_line('δ����ֵ���쳣��:'||sqlcode);r1:='δ����ֵ���쳣��:'||sqlcode;END;
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select FLOAT_ROUND(192.30234,NULL) into result;return result;end;language 'ploscar';
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select COS(id) into result from Test_proc01;return result;end;language 'ploscar';
create or replace procedure exesql2(a inout int) as begin         update test_tab_3 set column2=19.99 from test_tab_1 cross join test_view_1 where test_tab_3.column1=10;update test_tab_3 set column3=time '23:59:59', column2=test_tab_1.column2 from test_tab_1 cross join test_view_1 where test_tab_3.column1=10;return ;end;language 'ploscar';
create or replace procedure a()  as  declare  	result TEXT;begin 	select TEXTICLIKE(NULL,'WORLD') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure aplusb(i inout int,j in int)   as DECLARE     numberA int;BEGIN     numberA:= j;while numberA<0 or numberA>0 and not numberA=0 	LOOP 	  if numberA<0  	    then numberA:=numberA+1;i:=i-1;else numberA:=numberA-1;i:=i+1;end if;END LOOP;return;END;language 'ploscar';
create or replace package DBMS_AQ  as    enable  boolean default true;disable boolean default false;end DBMS_AQ;
CREATE OR REPLACE procedure TEST(r1 out text) is 	e7 exception;PRAGMA EXCEPTION_INIT(e7,-20999);BEGIN 	 	begin 	RAISE e7;EXCEPTION 	WHEN e7 THEN  		DBMS_OUTPUT.put_LINE('EXCEPTION e7:'||sqlcode);r1:='EXCEPTION e7:'||sqlcode;end;END;
create or replace procedure max_arg_1()  as begin	 	null;end;
create or replace procedure author_province(title inout varchar(32)) as DECLARE          _city varchar(32);_province varchar(32);BEGIN          select authors.city into _city from authors where authors.title=title;case _city             when cast('hangzhou' as varchar(32)) then _province:='zhejiang';when cast('shenzhen' as varchar(32)) then _province:='guangdong';when cast('xiamen' as varchar(32)) then _province:='fujian';else _province:='other';end case;title := _province;END;language 'ploscar';
create or replace function a(id TIME)return int as  declare  	result int;begin 	select "SECOND"(id) into result;return result;end;language 'ploscar';
CREATE OR REPLACE TRIGGER log_salary_increase   AFTER UPDATE OF New_salary ON employees   FOR EACH ROW BEGIN   INSERT INTO Emp_log (Emp_id, New_salary, Action)   VALUES (:OLD.Emp_id, :OLD.New_salary, :OLD.Action);END;
create or replace procedure p_numeric(adecimal decimal , bdecimal decimal(1000),cdecimal decimal(1000,10))   as declare    a int;adecimal1 decimal ;bdecimal1 decimal(1000);cdecimal1 decimal(1000,10);begin       null;adecimal1=adecimal;bdecimal1=bdecimal;cdecimal1=cdecimal;return ;end;language 'ploscar';
create or replace function Test_proc01()return float4 as  declare  	result float4;begin 	select FLOAT4_TRUNC(192.30234,NULL) into result;return result;end;language 'ploscar';
create  or  replace  trigger   triggerddl11   before grant   on database          BEGIN                insert into test values(2);END;
create or replace procedure aplusb(i inout int,j in int) as DECLARE      numberA int;BEGIN      numberA:= 0;LOOP 	    IF numberA = j THEN                EXIT;ELSE               numberA:= numberA+1;i:=i+1;END IF;END LOOP;END;language 'ploscar';
create or replace trigger test6 before update on lyj for each row begin insert into test values(1);end;
CREATE OR REPLACE function pltest() returns int as declare abc int;begin    abc =10;case abc  when  then abc=2;return 3;when null then abc=4;return 4;end case;return abc;end;
create or replace function f(s varchar2,call_pos int) return varchar2 is     rec_tab dbms_sql.desc_tab;c number;col_cnt int;col_num number;x int;a boolean;begin     if 1 = call_pos then         a := dbms_sql.is_open(c);end if;c := dbms_sql.open_cursor;if 2 = call_pos then         a := dbms_sql.is_open(c);end if;dbms_sql.parse(c, s, dbms_sql.native);if 3 = call_pos then         a := dbms_sql.is_open(c);end if;x := dbms_sql.execute(c);if 4 = call_pos then         a := dbms_sql.is_open(c);end if;col_num := rec_tab.first;if (col_num is not null) then          loop             dbms_output.put_line(lower(rec_tab(col_num).col_name));col_num := rec_tab.next(col_num);exit when (col_num is null);end loop;end if;dbms_sql.CLOSE_CURSOR(c);if 5 = call_pos then         a := dbms_sql.is_open(c);end if;if a then         return 'TRUE';else         return 'FALSE';end if;end;
create  or  replace  trigger   triggerddl11   after grant   on schema          BEGIN                insert into test values(2);END;
create or replace procedure default_arg_proc(a in int,b in int :=123,c int,d in varchar(20) default 'XXX') as begin raise notice'a is %',a;if b = 123 then raise notice'b is using its default value %',b;else raise notice'b is %',b;end if;raise notice'c is %',c;if d = 'XXX' then raise notice'd is using its default value %',d;else raise notice'd is %',d;end if;end;language 'ploscar';
create or replace package body test_3 as 	function exec_test return int as 	declare n int;begin 		test_1.private_pro();return 1;end exec_test;end test_3;
create or replace procedure test()   is  declare 	t_id lyj_package.type_id;kk int;begin   t_id(1):=2;kk:=t_id(1);DBMS_OUTPUT.PUT_LINE(kk);end;
create or replace function Test_proc01()return int2 as  declare  	result int2;begin 	select INT2LARGER(501::int2,1233::int2) into result;return result;end;language 'ploscar';
create or replace procedure P_TIME(FDATE1 DATE  ,FTime1 time ,Ftimestamp1 timestamp default timestamp '2004-11-20 00:00:00') AS declare  FDATE DATE;FTime time;Ftimestamp timestamp;BEGIN     FDATE=FDATE1;FTime=FTime1;Ftimestamp=Ftimestamp1;RETURN ;END;language 'ploscar';
create or replace function a()return text as  declare  	result text;begin 	select VERSION() into result;return result;end;language 'ploscar';
create or replace trigger test8 before insert on lyj for statement begin update lyj set a=8;end;
create function YF_20140626_001_FUNC1(data in VARBINARY(800), key in VARBINARY(800))      return VARBINARY(800) is begin         return DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(data, key);end YF_20140626_001_FUNC1;
create or replace procedure a()  as  declare  	result text;begin 	select REPLACE(b,c,d) into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace function j return int is     a int;begin     select xx into a from test_table;return a;exception when others then         return 1;end;
create or replace function aplusb(i in int,j in int) return int as DECLARE      number1 int;num int;BEGIN           number1:= j;num := i;while number1>0 or number1<0 	LOOP 	  if number1<0  	    then number1:=number1+1;num:=num-1;else number1:=number1-1;num:=num+1;end if;END LOOP;return num;END;language 'ploscar';
CREATE OR REPLACE PROCEDURE print()  AS   BEGIN             FOR i IN REVERSE 1..10 LOOP                      RAISE NOTICE 'i is %', i;END LOOP;RETURN;END;LANGUAGE 'PLOSCAR';
CREATE OR REPLACE PROCEDURE DIVIDE(I1 INT, I2 INT,I3 out TEXT)  AS BEGIN 	I3 := 'I1/I2�Ľ��Ϊ��'||I1/I2;END;
create or replace package forward as   procedure no_significance(unique_parm out int) ;end;
create or replace function a1()return text as  declare  	result text;begin 	select LPAD(b, 10, '?*') into result from t_text where a=1;return result;exception when others then 	begin 		return '';end;end;language 'ploscar';
CREATE OR REPLACE function pltest() returns int as declare aaa int;begin         <<zhangsan>>for i in 1..null loop 	aaa = 1;exit zhangsan;end loop;return 1;end;
create or replace trigger triggerddl121 before alter on lyj.schema  BEGIN  insert into sysdba.test_tt values(3);END;
CREATE OR REPLACE PROCEDURE COLLECTION_TEST()  IS DECLARE 	TYPE COLLECTION_VAR IS VARRAY(20) OF VARCHAR2(10);MyTab0 COLLECTION_VAR := COLLECTION_VAR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);MyTab MyTab0%TYPE;print VARCHAR2(20);BEGIN 	MyTab := COLLECTION_VAR(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);MyTab(10) := 'old';DBMS_OUTPUT.PUT_LINE('MyTab(10)��ʼֵΪ��		OLD	==> '||MyTab(10));MyTab(10) := 'NEW';DBMS_OUTPUT.PUT_LINE('MyTab(10)��ֵΪ��		NEW	==> '||MyTab(10));MyTab(1) := 'old_1';MyTab(2) := 'old_2';MyTab(3) := 'old_3';MyTab(4) := 'old_4';MyTab(5) := 'old_5';MyTab(6) := 'old_6';MyTab(7) := 'old_7';MyTab(8) := 'old_8';MyTab(9) := 'old_9';print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		10	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		1	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		10	==> '||PRINT);print := MyTab.NEXT(5);DBMS_OUTPUT.PUT_LINE(' MyTab.NEXT(5)ӦΪ:		6	==> '||PRINT);print := MyTab.PRIOR(0);DBMS_OUTPUT.PUT_LINE(' MyTab.PRIOR(0)ӦΪ:		��	==> '||PRINT);MyTab.EXTEND;print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		11	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		11	==> '||PRINT);MyTab.EXTEND(5,1);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		16	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		16	==> '||PRINT);MyTab.TRIM(3);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		13	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		13	==> '||PRINT);MyTab.DELETE;print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		0	==> '||PRINT);END;
CREATE OR REPLACE PROCEDURE P1 AS 	TYPE COLLECTION IS TABLE OF T1%ROWTYPE;TAB COLLECTION;V1	T1.ID%TYPE;V2	T1.NUM%TYPE;V3	T1.NAME%TYPE;ITERATIONS CONSTANT PLS_INTEGER := 5;K INT;BEGIN 	EXECUTE IMMEDIATE 'UPDATE T1 SET NUM = 100,NAME = ''NEW'' RETURNING ID,NUM,NAME INTO :1' 	RETURNING BULK COLLECT INTO TAB;K := TAB.FIRST;DBMS_OUTPUT.PUT_LINE('== TAB ==');DBMS_OUTPUT.PUT_LINE(TAB.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('== INDEX ' || K ||' == ');V1 := TAB(K).ID;V2 := TAB(K).NUM;V3 := TAB(K).NAME;DBMS_OUTPUT.PUT_LINE(' ID   ' || V1);DBMS_OUTPUT.PUT_LINE(' NUM  ' || V2);DBMS_OUTPUT.PUT_LINE(' NAME ' || V3);K	:= TAB.NEXT(K);END LOOP;END;
create or replace function Test_proc01()return float8 as  declare  	result float8;id float8:=0.3;begin 	select acos(id) into result;return result;end;language 'ploscar';
create or replace procedure p_name(time int null,j int not null default 100)   is  declare        sum int;begin 	sum = time+j;return;end;language 'ploscar';
create or replace function Test_proc01()return int4 as  declare  	result int4;id int4:=5;begin 	select factorial(id) into result;return result;end;language 'ploscar';
CREATE OR REPLACE FUNCTION test_array_f2 () return varchar(8)[1000] IS temp_array varchar(8)[1000];BEGIN temp_array[1] := '123456';temp_array[2] := '123456';temp_array[3] := '123456';RETURN temp_array;END;
create or replace package test  as 	procedure t1();end test;
create or replace procedure myfunc(inout int,int)  as declare         aa text;begin          perform test_type_conver00($1);return ;end;language 'ploscar';
CREATE OR REPLACE FUNCTION myfunc(a int, b int, c int) RETURN INT AS #option dump BEGIN 	EXECUTE IMMEDIATE 'PREPARE p1(TIMESTAMP) AS SELECT * FROM T1 WHERE TC1 > ? AND TC2 > ' || c ;RETURN a;END;LANGUAGE 'ploscar';
create or replace package body b  as 	procedure p2(r1 out int) as 	BEGIN 		 a.p1(r1);END;END;
CREATE OR REPLACE function pltest() returns int as declare aa student%rowtype;begin  	return;end;
create or replace procedure ppp1 is      a int;begin     select xx into a from test_table;end;
create or replace procedure tttt() as begin 	dbms_output.put_line(0);end;language 'ploscar';
create or replace procedure JOB_P1 as begin   INSERT INTO t1 VALUES (79358);end;
create or replace function f_15148() return int as begin     return f_15148_2();end;
create or replace function a()return int as  declare  	result int;begin 	select SYS_GET_PAGE_SIZE() into result;return result;end;language 'ploscar';
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select SETSEED(id) into result from Test_proc01;return result;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE NESTEDTABLE IS VARRAY(20) OF varchar2(10);MYNESTED NESTEDTABLE := NESTEDTABLE('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10');I INT;BEGIN 	DBMS_OUTPUT.PUT_LINE('Count is : '||MYNESTED.COUNT);MYNESTED.DELETE;DBMS_OUTPUT.PUT_LINE('Count is : '||MYNESTED.COUNT);MYNESTED.EXTEND(5);i	:= MYNESTED.FIRST;WHILE i IS NOT NULL LOOP 		dbms_output.put_line('Data of '|| i || ' is '||MYNESTED(i));i	:= MYNESTED.NEXT(i);END LOOP;END;
CREATE or replace procedure somefunc()   AS  DECLARE quantity int := 30;BEGIN RAISE NOTICE 'Quantity here is %', quantity;quantity = 50;DECLARE quantity int := 80;BEGIN RAISE NOTICE 'Quantity here is %', quantity;END;RAISE NOTICE 'Quantity here is %', quantity;RETURN ;END;language 'ploscar';
create or replace function a() return text as declare   b text:= 'COLES';c text:= 'COLEMAN';BEGIN   IF b>c THEN     return( b || ' is greater than ' || c );ELSE     return( c || ' is greater than ' || b );END IF;END;language 'ploscar';
CREATE TRIGGER Tri_tri01 BEFORE INSERT ON Tri_tab01 FOR EACH ROW  BEGIN 	delete from Tri_tab01 where c1=10;delete from Tri_tab02 where c1=10;END;
create or replace procedure a(id in text)  as  declare  	result int;begin 	select PATINDEX('%as%',id) into result;dbms_output.put_line(result);end;language 'ploscar';
create  or  replace  trigger   triggerddl11   before grant   on schema          BEGIN                insert into test values(2);END;
create or replace function Test_proc01()return float(50) as  declare  	result float(50);begin 	select abs(-23) into result;return result;end;language 'ploscar';
CREATE or replace PACKAGE DBMS_UTILITY  IS   PROCEDURE ANALYZE_SCHEMA(SCHEMA VARCHAR2, METHOD VARCHAR2,  					   ESTIMATE_ROWS NUMBER DEFAULT 0,  					   ESTIMATE_PERCENT NUMBER DEFAULT 0,  					   METHOD_OPT VARCHAR2 DEFAULT '') ;PROCEDURE ANALYZE_DATABASE(METHOD VARCHAR2,  						 ESTIMATE_ROWS NUMBER DEFAULT 0,  						 ESTIMATE_PERCENT NUMBER DEFAULT 0,  						 METHOD_OPT VARCHAR2 DEFAULT '');PROCEDURE ANALYZE_INDEX(METHOD VARCHAR2);END;
create or replace procedure a() as declare   RETURN INTEGER;begin   null;end;
create or replace procedure a(id in text)  as  declare  	result int;begin 	CREATE SEQUENCE SEQ;SELECT nextval(id) into result;SELECT CURRVAL(id) into result;drop SEQUENCE SEQ;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure whileloop(i inout int,j inout int)   as DECLARE     numberA int;num int;boolh boolean;BEGIN         numberA:= j;while NOT whileloopED(numberA) LOOP 	  if numberA<0  	    then numberA = 20;i=i+1;else numberA:=numberA-1;i:=i+1;end if;END LOOP;return;END;language 'ploscar';
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select acos(id) into result from Test_proc01;return result;end;language 'ploscar';
create function YF_20140626_001_FUNC1(data in VARCHAR2, key in VARCHAR2, iv in VARCHAR2)      return VARCHAR2 is begin         return DBMS_OBFUSCATION_TOOLKIT.DES3Encrypt(data, key, 1, iv);end YF_20140626_001_FUNC1;
CREATE OR REPLACE PROCEDURE print()   AS  BEGIN            FOR i IN REVERSE 10..1 LOOP                     RAISE NOTICE 'i is %', i;END LOOP;RETURN;END;LANGUAGE 'PLOSCAR';
create trigger a_column15 BEFORE update ON a FOR EACH ROW Begin 	RAISE NOTICE 'column_trigger() called: action = %, when = %, level = %', TG_OP, TG_WHEN, TG_LEVEL;insert into result values(0, TG_OP, TG_WHEN, TG_LEVEL);end;
create or replace function Test_proc01()return DOUBLE PRECISION as  declare  	result DOUBLE PRECISION;begin 	select DLOG10(0) into result;return result;end;language 'ploscar';
create or replace procedure add_one_in_assign(i inout int, j inout int) as DECLARE     ret int;BEGIN     j := j + 1;ret := i + add_one(i) + add_one(j);DBMS_OUTPUT.PUT_LINE(ret);END;language 'ploscar';
CREATE OR REPLACE function pltest() return int as aaa student%rowtype;begin 	select * into aaa from student;return aaa.aa;end;
create or replace package null  as   end null;
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select TRUNC(192.30234) into result;return result;end;language 'ploscar';
create or replace procedure proc1(_id inout tinyint) as declare       int11 CONSTANT int   := -2147483648;begin      case _id      when -128 then            _id := -128;when 127 then            _id := 127;when 0 then                _id := 0;else           _id := 99;end case;end;language 'ploscar';
create or replace package body scope  as   procedure proc(r1 out varchar(50),r2 out varchar(50))    is      var varchar(50) :='variable in proc';begin    	r1:=var;r2:=scope.var;dbms_output.put_line(var);dbms_output.put_line(scope.var);end;end scope;
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select ROUND(192.30234,-2) into result;return result;end;language 'ploscar';
create or replace function Test_proc01()return int4 as  declare  	result int4;begin 	select INT4LARGER(-22.3,-10.5) into result;return result;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE COL_TYP IS TABLE OF INT;MyTab COL_TYP := COL_TYP(1,2,3,4,5,6,7,8,9,10);K INT;BEGIN 	K := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE('Count is : '||K);MyTab.EXTEND;K := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE('Count is : '||K);K	:= MYTAB.FIRST;WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||MYTAB(K));K	:= MYTAB.NEXT(K);END LOOP;END;
create or replace procedure dyn_exec(a1 int,r1 out int,r2 out varchar(20)) as declare v1 int := 10;v2 varchar(20) := 'hello';v3 date := '1986-07-29';begin 	raise notice '% %',v1,v2;execute immediate 'update mytable set c = :1 where a = :2 Returning a,b into :3,:4' 	using v3,a1 	returning into v1,v2;raise notice 'return % %',v1,v2;r1:=v1;r2:=v2;end;
create or replace function p_exeText(aid int) return varchar(70) as declare    adateR data_type1%rowtype;begin    select * into adateR from data_type1 where field1 ='ling1';if found then       CASE AID         when 1 then   return adateR.field1;when 2 then   return adateR.field2;when 3 then   return adateR.field3;else          return adateR.field8;end case;else        return 1234;end if;end;language 'ploscar';
CREATE OR REPLACE function pltesta() returns int as declare i int;j int;rec student%rowtype;begin 	i = 1;select * into rec from student limit 1;update student set a = 1 where a= i and b = pltest(3,rec);return 1;end;
create or replace function getstringval_test(xmlVar xmltype) return varchar2 is   xmlStringData varchar2(4000);begin     xmlStringData := xmlVar.getstringval();return xmlStringData;end;
CREATE OR REPLACE PROCEDURE for_loop()  AS  BEGIN FOR i IN REVERSE 20..1 LOOP  if  i>11 then     continue;end if;RAISE NOTICE 'i is %', i;END LOOP;RETURN;END;LANGUAGE 'PLOSCAR';
create procedure test(i1 in int,i2 in int) as declare 	sql_ text:='alter table t_hash modify partition p4 add values(';begin 	for i in i1..i2-1 loop 		sql_:=sql_||i||',';end loop;sql_:=sql_||i2||');';execute immediate sql_;end;
create  or  replace  trigger   triggerddl11    after revoke  on lyj.schema BEGIN 	insert into lyjlyj values(2);END;
create or replace procedure exesql2(a inout int)  as begin      update Type_800_tab2 set column1=-3.4e+38,column11=3.4e+38,column21='�Ѿ�����update', column31=3.14159+sin(1.5),column32=3.14159,column33=3.14159, column51=3.14159+sin(1.5),column52=-1.79e+308,column53=1.79e+308, column71='����upd',column72='�Ѿ�upd', column91=12345678,column92=-2147483648,column93=2147483647, column101=CURRENT_DATE,column131='23:59:59',column151=CURRENT_TIMESTAMP, column171=-9223372036854775808+12345678,column172=-9223372036854775808,column173=9223372036854775807, column191=-32769,column192=32767,column193=12345, column211=-128,column212=127,column213=123   where Type_800_tab2.column0 between 9123624 and 9993485;return ;end;language 'ploscar';
create or replace function Test_proc01(id int1)return int1 as  declare  	result int1;begin 	select INT1UP(id) into result;return result;end;language 'ploscar';
create or replace function Test_proc01()return DOUBLE PRECISION as  declare  	result DOUBLE PRECISION;begin 	select DPOW(-3,-2) into result;return result;end;language 'ploscar';
create or replace function func_test() return integer as begin 	return 0;end;
CREATE OR REPLACE TRIGGER T_TEST BEFORE INSERT ON TEST FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('*********************************');END;
create or replace procedure p_name(create int null,j int not null default 100)   is  declare        sum int;begin 	sum = create+j;return;end;language 'ploscar';
create trigger a_column1 AFTER update of id ON a FOR EACH ROW  begin 	DBMS_OUTPUT.PUT_LINE('Trig exec');end;
CREATE OR REPLACE PROCEDURE P1 AS 	TYPE IDTAB IS VARRAY(200) OF T1.ID%TYPE;TYPE NUMTAB IS TABLE OF T1.NUM%TYPE INDEX BY PLS_INTEGER;TYPE NAMETAB IS TABLE OF T1.NAME%TYPE INDEX BY PLS_INTEGER;TYPE INDICESTAB IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;PARMCOLL IDTAB;IDS	   IDTAB;NUMS   NUMTAB;NAMES  NAMETAB;INDICE INDICESTAB;ITERATIONS CONSTANT PLS_INTEGER := 5;K INT;BEGIN 	PARMCOLL := IDTAB();PARMCOLL.EXTEND(100);FOR I IN 1 .. 100 LOOP 		PARMCOLL(I) := I;END LOOP;INDICE(100) := 11;INDICE(300) := 13;INDICE(500) := 15;FORALL II IN VALUES OF INDICE 		UPDATE T1 SET NAME = 'NEW' WHERE ID = PARMCOLL(II) RETURNING ID,NUM,NAME BULK COLLECT INTO IDS,NUMS,NAMES;K := IDS.FIRST;DBMS_OUTPUT.PUT_LINE('== IDS ==');DBMS_OUTPUT.PUT_LINE(IDS.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||IDS(K));K	:= IDS.NEXT(K);END LOOP;K := NUMS.FIRST;DBMS_OUTPUT.PUT_LINE('== NUMS ==');DBMS_OUTPUT.PUT_LINE(NUMS.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||NUMS(K));K	:= NUMS.NEXT(K);END LOOP;K := NAMES.FIRST;DBMS_OUTPUT.PUT_LINE('== NAMES ==');DBMS_OUTPUT.PUT_LINE(NAMES.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||NAMES(K));K	:= NAMES.NEXT(K);END LOOP;END;
CREATE or replace function p_goto() RETURN INTEGER AS  DECLARE    foo INTEGER := 4;BEGIN	 	  goto wangqi;CASE 1             WHEN 1 THEN              <<wangqi>>               foo=foo+1;RAISE NOTICE 'foo IS %',foo;WHEN 2 THEN              foo=foo-1;RAISE NOTICE 'foo IS %',foo;ELSE              foo=foo-1;RAISE NOTICE 'foo IS %',foo;END CASE;foo=20;RAISE NOTICE 'foo IS %',foo;return 0;END;LANGUAGE 'ploscar';
create or replace function fcsTest10(i int) return int is     a integer;begin     if i = 1 then         a := 10/0;return a;else         return fcsTest10(i - 1);end if;end;
CREATE OR REPLACE PROCEDURE P1 AS 	TYPE REC IS RECORD( 		FIELD1 T1.ID%TYPE DEFAULT 10, 		FIELD2 T1.NUM%TYPE := 100, 		FIELD3 T1.NAME%TYPE := 'OLD');TYPE IDTAB IS VARRAY(20) OF REC;R1 REC;R2 REC;IDS IDTAB;IDS0 IDTAB;K INT;V1	T1.ID%TYPE;V2	T1.NUM%TYPE;V3	T1.NAME%TYPE;BEGIN 	IDS0 := IDTAB(R1,R1);IDS0.EXTEND(3);IDS0(3) := R1;IDS0(4) := R2;IDS0(5) := IDS0(1);IDS0.DELETE;IDS0.EXTEND(10);IDS := IDS0;K := IDS.FIRST;DBMS_OUTPUT.PUT_LINE('.');DBMS_OUTPUT.PUT_LINE('== IDS ==');DBMS_OUTPUT.PUT_LINE(IDS.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('== INDEX ' || K ||' == ');V1 := IDS(K).FIELD1;V2 := IDS(K).FIELD2;V3 := IDS(K).FIELD3;DBMS_OUTPUT.PUT_LINE(' FIELD1 : ' || V1);DBMS_OUTPUT.PUT_LINE(' FIELD2 : ' || V2);DBMS_OUTPUT.PUT_LINE(' FIELD3 : ' || V3);K	:= IDS.NEXT(K);END LOOP;R1.FIELD1 := 100;R1.FIELD2 := 100;R1.FIELD3 := 'NEW';IDS(3) := R1;IDS(4).FIELD1 := '111111';IDS(4).FIELD3 := 'NEWNEW';IDS('1') := IDS(3);IDS(1+1) := NULL;IDS(8) := R1;IDS(9) := R1;IDS(9+1) := IDS(8);IDS.TRIM(2);IDS.EXTEND(1,8);IDS.EXTEND(1);IDS(10) := R1;K := IDS.FIRST;DBMS_OUTPUT.PUT_LINE('.');DBMS_OUTPUT.PUT_LINE('== IDS ==');DBMS_OUTPUT.PUT_LINE(IDS.COUNT);WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('== INDEX ' || K ||' == ');V1 := IDS(K).FIELD1;V2 := IDS(K).FIELD2;V3 := IDS(K).FIELD3;DBMS_OUTPUT.PUT_LINE(' FIELD1 : ' || V1);DBMS_OUTPUT.PUT_LINE(' FIELD2 : ' || V2);DBMS_OUTPUT.PUT_LINE(' FIELD3 : ' || V3);K	:= IDS.NEXT(K);END LOOP;END;
create or replace procedure PRecord(_id int) as     DECLARE             Rdatatype1 data_type1%ROWTYPE ;Rdatatype2 data_type2%ROWTYPE ;Rdatatype3 data_type3%ROWTYPE ;Rdatatype4 data_type4%ROWTYPE ;RTboolean Tboolean%ROWTYPE  ;RTbit Tbit%ROWTYPE  ;Rdatatype11 data_type1%ROWTYPE ;Rdatatype21 data_type2%ROWTYPE ;Rdatatype31 data_type3%ROWTYPE ;Rdatatype41 data_type4%ROWTYPE ;RTboolean1 Tboolean%ROWTYPE ;RTbit1 Tbit%ROWTYPE ;begin          null;SELECT * INTO Rdatatype1   FROM data_type1 WHERE field1 = 'ling1';SELECT * INTO Rdatatype1   FROM data_type2 WHERE field1 = 'ling1';SELECT * INTO Rdatatype1   FROM data_type3 WHERE field1 = 'ling1';SELECT * INTO Rdatatype1   FROM tbit where akey=3;SELECT * INTO Rdatatype4   FROM data_type4 WHERE field1 = 'ling1';SELECT * INTO rtbit  from tbit where akey=3;SELECT * INTO rtboolean  from tboolean where field1=1234567;return ;end;language 'ploscar';
CREATE TRIGGER tria after insert     on aaaa for each row  begin       	 	insert into aaaa values (0);end;
CREATE OR REPLACE function pltest() returns int as declare abc int;begin    abc =10;case 0 when 0 then abc=2;return 3;when null then abc=4;return 4;end case;return abc;end;
CREATE OR REPLACE PROCEDURE p_1 IS       sql_id INTEGER;sql_stmt VARCHAR2(600);v1 VARCHAR2(20) := '';v2 VARCHAR2(20) := '';BEGIN     sql_id := DBMS_SQL.OPEN_CURSOR();sql_stmt := 'select c2 from test';DBMS_SQL.PARSE(sql_id, sql_stmt, DBMS_SQL.NATIVE);DBMS_SQL.DEFINE_COLUMN(sql_id, 1, v1, 20);equal_int(DBMS_SQL.EXECUTE(sql_id), 0);equal_int(DBMS_SQL.FETCH_ROWS(sql_id), 1);DBMS_SQL.COLUMN_VALUE(sql_id, 1, v2);DBMS_SQL.CLOSE_CURSOR(sql_id);END;
create or replace procedure Pcursor(_id int)   as  declare       curManyConnect1 CURSOR FOR select * from (select * from test_tab_1) as tab_1 cross join test_tab_2 cross join test_tab_3;curManyConnect2 CURSOR is select * from test_view_1 inner join test_tab_2 using(column1) join test_tab_3 using(column1);curManyConnect3 CURSOR is select * from test_view_1 join test_view_2 using(column1) inner join test_tab_3 on test_view_2.column1=test_tab_3.column1;curManyConnect4 CURSOR is select * from test_view_1 join test_view_2 on test_view_1.column1=test_view_2.column1 inner join test_tab_3 on test_view_1.column1=test_tab_3.column1;curManyConnect5 CURSOR is select * from test_view_1 join test_view_2 on test_view_1.column1=test_view_2.column1 and test_view_1.column2=test_view_2.column2 join test_tab_2 on test_view_1.column1=test_tab_2.column1 and test_view_2.column2=test_tab_2.column2;curManyConnect6 CURSOR is select * from test_view_1 join test_view_2 on test_view_1.column1=test_view_2.column1 and test_view_1.column2=test_view_2.column2 join test_tab_2 on test_view_1.column1=test_tab_2.column1;curManyConnect7 CURSOR is select * from (select * from test_view_1) as tab_1 natural join test_tab_2 natural join test_view_1;curManyConnect8 CURSOR is select * from test_view_2 natural left join test_tab_2 natural right join test_view_1;curManyConnect9 CURSOR is select * from test_tab_1 natural left join test_tab_2 natural join test_view_1;curManyConnect10 CURSOR is select * from test_tab_1 natural right join test_tab_2 natural join test_view_1;curManyConnect11 CURSOR is select * from test_tab_1 inner join test_tab_2 on test_tab_1.column1=test_tab_2.column2;curManyConnect12 CURSOR is select * from test_tab_1 inner join test_tab_2 on test_tab_1.column1=test_tab_2.column3;curManyConnect13 CURSOR is select * from test_tab_1 inner join test_tab_2 on test_tab_1.column4=test_tab_2.column3;curManyConnect1r test_tab_1%rowtype;curManyConnect2r test_tab_1%rowtype;curManyConnect3r test_tab_1%rowtype;curManyConnect4r test_tab_1%rowtype;curManyConnect5r test_tab_1%rowtype;curManyConnect6r test_tab_1%rowtype;curManyConnect7r test_tab_1%rowtype;curManyConnect8r test_tab_1%rowtype;curManyConnect9r test_tab_1%rowtype;curManyConnect10r test_tab_1%rowtype;curManyConnect11r test_tab_1%rowtype;curManyConnect12r test_tab_1%rowtype;curManyConnect13r test_tab_1%rowtype;begin       OPEN curManyConnect1;OPEN curManyConnect2;OPEN curManyConnect3;OPEN curManyConnect4;OPEN curManyConnect5;OPEN curManyConnect6;OPEN curManyConnect7;OPEN curManyConnect8;OPEN curManyConnect9;OPEN curManyConnect10;OPEN curManyConnect11;OPEN curManyConnect12;OPEN curManyConnect13;FETCH curManyConnect1 into  curManyConnect1r;FETCH curManyConnect2 into  curManyConnect2r;FETCH curManyConnect3 into  curManyConnect3r;FETCH curManyConnect4 into  curManyConnect4r;FETCH curManyConnect5 into  curManyConnect5r;FETCH curManyConnect6 into  curManyConnect6r;FETCH curManyConnect7 into  curManyConnect7r;FETCH curManyConnect8 into  curManyConnect8r;FETCH curManyConnect9 into  curManyConnect9r;FETCH curManyConnect10 into  curManyConnect10r;FETCH curManyConnect11 into  curManyConnect11r;FETCH curManyConnect12 into  curManyConnect12r;FETCH curManyConnect13 into  curManyConnect13r;raise notice 'column is %',curManyConnect1r.column1;raise notice 'column is %',curManyConnect2r.column1;raise notice 'column is %',curManyConnect3r.column1;raise notice 'column is %',curManyConnect4r.column1;raise notice 'column is %',curManyConnect5r.column1;raise notice 'column is %',curManyConnect6r.column1;raise notice 'column is %',curManyConnect7r.column1;raise notice 'column is %',curManyConnect8r.column1;raise notice 'column is %',curManyConnect9r.column1;raise notice 'column is %',curManyConnect10r.column1;raise notice 'column is %',curManyConnect11r.column1;raise notice 'column is %',curManyConnect12r.column1;raise notice 'column is %',curManyConnect13r.column1;close curManyConnect1;close curManyConnect2;close curManyConnect3;close curManyConnect4;close curManyConnect5;close curManyConnect6;close curManyConnect7;close curManyConnect8;close curManyConnect9;close curManyConnect10;close curManyConnect11;close curManyConnect12;close curManyConnect13;return;end;language 'ploscar';
create or replace function a() return timestamp as  declare  	result timestamp;begin 	SELECT CURTIMESTAMP() into result;return result;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE COL_TYP IS VARRAY(20) OF INT NOT NULL;MyTab COL_TYP := COL_TYP(1,2,3,4,5,6,7,8,9,10);K INT;BEGIN 	K := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE('Count is : '||K);MyTab.EXTEND;K := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE('Count is : '||K);K	:= MYTAB.FIRST;WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||MYTAB(K));K	:= MYTAB.NEXT(K);END LOOP;END;
create or replace procedure p_name(case int null,j int not null default 100)   is  declare        sum int;begin 	sum = case+j;return;end;language 'ploscar';
CREATE OR REPLACE TRIGGER TRG1 BEFORE UPDATE ON T1 FOR EACH ROW BEGIN 	:NEW.A := 100;END;
create or replace procedure a()  as  declare  	result int;begin 	SELECT CHAR_LENGTH('niu') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace function myfactor123(i integer) return bigint as begin 		if (i=0) then 			return 1;end if;return i*myfactor123(i-1);end;language 'ploscar';
create or replace function test_func6 return int is     seq_value NUMBER;begin     seq_value := sysdba.t1_sq1.currval;return seq_value;end;
create or replace function Test_proc01()return int4 as  declare  	result int4;begin 	select INT4SMALLER(-22::int4,-40::int4) into result;return result;end;language 'ploscar';
create or replace function Test_proc01()return DECIMAL(10,4) as  declare  	result DECIMAL(10,4);begin 	select INT2_TRUNC(19::int1,-2::int4) into result;return result;end;language 'ploscar';
create or replace procedure a(id in char(15))  as  declare  	result int;begin 	select POSITION('as' in id) into result;dbms_output.put_line(result);end;language 'ploscar';
CREATE OR REPLACE PROCEDURE ptest9()   AS BEGIN   DELETE FROM score;DELETE FROM student;COMMIT;INSERT INTO student VALUES(1, 'a');INSERT INTO student VALUES(2, 'b');INSERT INTO score VALUES(1, 1);commit;SAVEPOINT DO_INSERT10;INSERT INTO score VALUES(1, 2);ROLLBACK TO DO_INSERT10;INSERT INTO score VALUES(1,3);insert into score values(2,1);delete from student where sid=1;insert into score values(2,2);commit;END;
create or replace procedure dyn_exec(a1 int) as declare v1 int;v2 varchar(20);v3 date;begin 	execute immediate 'select * from mytable where a = :1' 	into  	using a1;raise notice '% % %',v1,v2,v3;end;language 'ploscar';
create or replace procedure pro_trunc4(tn varchar(10),before_count out int,after_count out int) as declare 	part_name varchar(100)='';part_id int;begin 	 select distinct PARTNAME into part_name from v_sys_tab_partitions where RELNAME=tn and PARTNAME like 'SYS_P%' and oid=(select min(oid) from v_sys_tab_partitions where RELNAME=tn and PARTNAME like 'SYS_P%' );execute immediate 'select count(*) from '||tn into before_count;execute immediate 'alter table '||tn||' truncate partition '||part_name ;execute immediate 'select count(*) from '||tn into after_count;end;language 'ploscar';
create or replace procedure a(id in double precision)  as  declare  	result varchar(10);begin 	select TO_CHAR(id,'000.00') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace function test_f1(i inout int) return void as begin 	select * from test_table;return ;end;
create or replace trigger tg after insert on test for each row begin     q;exception when others then         insert into test1 values(dbms_utility.format_error_stack);end;
create or replace function Test_proc01()return float8 as  declare  	result float8;id float8:=25.56;begin 	select SETSEED(id) into result;return result;end;language 'ploscar';
CREATE OR REPLACE function GetIntRand(startInt INT, endInt INT) RETURN TEXT AS DECLARE 	sqlStr TEXT;BEGIN	 	sqlStr = (FLOOR(RAND() * (endInt - startInt)) + startInt)::INT::TEXT;RETURN sqlStr;END;language 'ploscar';
create or replace function table()   return int as begin   raise notice 'function Test_proc';end;
create or replace function a123(id int,t text,z text) return int as begin   return id;end;language 'ploscar';
create or replace procedure a()  as  declare  	result text;begin 	select LPAD(b,10,d) into result from a;dbms_output.put_line(result);end;language 'ploscar';
CREATE OR REPLACE PROCEDURE ptest12()   AS BEGIN   DELETE FROM testsp;INSERT INTO testsp VALUES(120);SAVEPOINT DO_INSERT120;INSERT INTO testsp VALUES(121);SAVEPOINT DO_INSERT120;INSERT INTO testsp VALUES(122);ROLLBACK TO DO_INSERT120;END;LANGUAGE 'PLOSCAR';
create or replace function Test_proc01()return int4 as  declare  	result int4;begin 	select factorial(id) into result from Test_proc01;return result;end;language 'ploscar';
create or replace function a()return int as  declare  	result int;begin 	SELECT "MINUTE"('13:30:23') into result;return result;end;language 'ploscar';
create or replace procedure test() as declare s varchar(512);begin raise ZERO_DIVIDE;return;exception when others then  begin 	raise notice 'xiuxiu 2';select sqlerrm(848297990) into s;raise notice '%',s;begin                    	execute raise_application_error(-20004,'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012');exception when others then  	raise notice 'xiuxiu 4';select sqlerrm(-20004) into s;raise notice '%',s;end;return;end;end;language 'ploscar';
create or replace procedure pro_trunc1(tn varchar(10),before_count out int,after_count out int) as declare 	part_name varchar(100)='';part_id int;begin    execute immediate 'select count(*) from '||tn into before_count;select distinct SUBPARTNAME into part_name from v_sys_tab_subpartitions where RELNAME=tn and SUBPARTNAME like 'SYS_SP%' and oid=(select max(oid) from v_sys_tab_subpartitions where RELNAME=tn and SUBPARTNAME like 'SYS_SP%' );execute immediate 'alter table '||tn||' truncate subpartition '||part_name ;execute immediate 'select count(*) from '||tn into after_count;end;language 'ploscar';
create or replace procedure exesql2(a inout int) as begin     update Type_800_tab1 set column0=-column0+9993485 WHERE column0 BETWEEN -9999990 AND -5999990 ;update Type_800_tab1 set column0=column0-9993485 WHERE column0 BETWEEN -9999990 AND -5999990 ;return ;end;language 'ploscar';
CREATE OR REPLACE function pltest(a inout int,b inout int ) RETURN int AS  BEGIN 	a=8;b=9;return a;END;
create or replace package test as a int;end;
create or replace  function f(i1 varchar2 ) return varchar2 as begin     raise notice '%',i1;return 'aaaaaaaaaaaaaaaaaaaaaa';end;
CREATE OR REPLACE PROCEDURE COLLECTION_TEST()  IS DECLARE 	TYPE COLLECTION_VAR IS TABLE OF VARCHAR2(10);MyTab COLLECTION_VAR := COLLECTION_VAR();print VARCHAR2(20);BEGIN 	MyTab.EXTEND(10);MyTab(10) := 'old';DBMS_OUTPUT.PUT_LINE('MyTab(10)��ʼֵΪ��		OLD	==> '||MyTab(10));MyTab(10) := 'NEW';DBMS_OUTPUT.PUT_LINE('MyTab(10)��ֵΪ��		NEW	==> '||MyTab(10));MyTab(1) := 'old_1';MyTab(2) := 'old_2';MyTab(3) := 'old_3';MyTab(4) := 'old_4';MyTab(5) := 'old_5';MyTab(6) := 'old_6';MyTab(7) := 'old_7';MyTab(8) := 'old_8';MyTab(9) := 'old_9';print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		10	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		1	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		10	==> '||PRINT);print := MyTab.NEXT(5);DBMS_OUTPUT.PUT_LINE(' MyTab.NEXT(5)ӦΪ:		6	==> '||PRINT);print := MyTab.NEXT(10);DBMS_OUTPUT.PUT_LINE(' MyTab.NEXT(10)ӦΪ:		��	==> '||PRINT);print := MyTab.PRIOR(5);DBMS_OUTPUT.PUT_LINE(' MyTab.PRIOR(5)ӦΪ:		4	==> '||PRINT);print := MyTab.PRIOR(0);DBMS_OUTPUT.PUT_LINE(' MyTab.PRIOR(0)ӦΪ:		��	==> '||PRINT);MyTab.DELETE(10);MyTab.DELETE(-9);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		9	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		1	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		9	==> '||PRINT);MyTab.DELETE(-4,6);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		3	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		7	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		9	==> '||PRINT);print := MyTab.NEXT(5);DBMS_OUTPUT.PUT_LINE(' MyTab.NEXT(5)ӦΪ:		7	==> '||PRINT);print := MyTab.NEXT(-5);DBMS_OUTPUT.PUT_LINE(' MyTab.NEXT(-5)ӦΪ:		7	==> '||PRINT);print := MyTab.PRIOR(7);DBMS_OUTPUT.PUT_LINE(' MyTab.PRIOR(7)ӦΪ:		��	==> '||PRINT);print := MyTab.PRIOR(20);DBMS_OUTPUT.PUT_LINE(' MyTab.PRIOR(20)ӦΪ:		9	==> '||PRINT);MyTab.EXTEND(2);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		5	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		7	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		12	==> '||PRINT);MyTab.TRIM(5);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		1	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		7	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		7	==> '||PRINT);MyTab.EXTEND(3);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		4	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		7	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		10	==> '||PRINT);MyTab.EXTEND(6,8);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		10	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		7	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		16	==> '||PRINT);MyTab.DELETE;print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		0	==> '||PRINT);MyTab.EXTEND(3);print := MyTab.COUNT;DBMS_OUTPUT.PUT_LINE(' MyTab.COUNTӦΪ:		3	==> '||PRINT);print := MyTab.FIRST;DBMS_OUTPUT.PUT_LINE(' MyTab.FIRSTӦΪ:		1	==> '||PRINT);print := MyTab.LAST;DBMS_OUTPUT.PUT_LINE(' MyTab.LAST ӦΪ:		3	==> '||PRINT);END;
create or replace procedure a(id in text)  as  declare  	result date;begin 	select TO_DATE(id,'YYYY&MMpDD') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure a(id in char(15))  as  declare  	result text;begin 	select QUOTE_LITERAL(id) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure p_11891(n int) as declare 	i int = 1;begin 	while i <= n loop 				insert into t_11891_1 values(1, repeat('a', 100));insert into t_11891_2 values(1, repeat('a', 100));i = i+1;end loop;end;
create or replace procedure a(b in varchar(10),c in text)  as  declare  	result char(20);begin 	select CONCAT(b,c) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace function proc1(_id int) return int as declare       int11 CONSTANT int   := -2147483648;int12 CONSTANT int  := 2147483647;int01 int;int02 int;smallint11 CONSTANT smallint := -2^15;smallint12 CONSTANT smallint  := 2^15-1;smallint01 smallint;smallint02 smallint;bigint11 CONSTANT bigint := -9223372036854775808;bigint12 CONSTANT bigint := 9223372036854775807;bigint01 bigint;bigint02 bigint;tinyint11  CONSTANT tinyint := -128;tinyint12 CONSTANT tinyint  :=127;tinyint01 tinyint;tinyint02 tinyint;float11 CONSTANT float := 3.4e+38;float12 CONSTANT float := -3.4e+38;float01 float;float02 float;doubleprecision11 CONSTANT double precision := 1.79e+308 ;doubleprecision12 CONSTANT double precision := -1.79e+308 ;doubleprecision01 double precision;doubleprecision02 double precision;real11 CONSTANT real := 3.4e+38;real12 CONSTANT real := -3.4e+38;real01 real;real02 real;DECIMAL11 CONSTANT decimal(1000) := 1234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111111111111112345678901234567890000000000000000000000000000000000000000000000000001111111111111111111111111111111234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111111111111112345678901234567890000000000000000000000000000000000000000000000000001111111111111111111111111111111234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111111111111112345678901234567890000000000000000000000000000000000000000000000000001111111111111111111111111111111234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111;DECIMAL12 CONSTANT decimal := 923456789012345678;DECIMAL13 CONSTANT decimal(1000,10) := 123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111111111111112345678901234567890000000000000000000000000000000000000000000000000001111111111111111111111111111111234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111111111111112345678901234567890000000000000000000000000000000000000000000000000001111111111111111111111111111111234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111111111111112345678901234567890000000000000000000000000000000000000000000000000001111111111111111111111111111111234567890123456789000000000000000000000000000000000000000000000000000111111111111111111111111111111123456789012345678900000000000000000000000000000000000000000000000000011111111111111111111.1111111111;decimal01 decimal(1000);decimal02 decimal;decimal03 decimal(1000,10);Fvarchar11 CONSTANT varchar(7000) := '12345678900000000000000000000000000000000000000000000000000000000000000000000000000000000000';Fvarchar01  varchar(7000) ;Fchar11 CONSTANT char(7000) := '12345678900000000000000000000000000000000000000000000000000000000000000000000000000000000000';Fchar01 char(7000);bit11 CONSTANT bit(3)  := B'000';bit01 bit(3);Time11 CONSTANT time  :=time '04:02:09';Time01 time;timestamp11 CONSTANT timestamp :=timestamp '2009-11-20 23:59:59';timestamp01 timestamp;begin      case _id      when -2147483648 then            return -2147483648;when 2147483647 then            return 2147483647;when 0 then                return 0;else           return 999999999;end case;end;language 'ploscar';
create or replace procedure perform(a in int )  as begin       null;end;language 'ploscar';
create or replace procedure test()   is  declare 	rowtype1 test1%rowtype;type lyj_record is record ( 	k1 rowtype1.k1%type:=11 , 	k2 varchar(10):='mmm') ;test lyj_record;begin 	 DBMS_OUTPUT.PUT_LINE('k1='||test.k1);DBMS_OUTPUT.PUT_LINE('k2='||test.k2);end;
create or replace procedure test_overload() as begin perform default_arg_func(1,10);end;language 'ploscar';
create or replace function f return lz%rowtype  is declare 	rw lz%rowtype;begin 	select * into rw from lz;return rw;end;
create or replace package Test_proc  as    var  int;const_var constant varchar(80) := 'Hello World!';function fun(a int) return  numeric;procedure pro(p numeric);cursor cur is select * from temp;invalid_rad exception;end Test_proc;
create trigger YF_20140827_001_TR1     before delete      on YF_20140827_001_TAB1     for each row  begin     raise notice 'YF_20140827_001_TR1 ... BEFORE ... DELETE ... 1';end;
create or replace function a()return text as  declare  	result TEXT;begin 	select DAYNAME(id) into result from a;return result;end;language 'ploscar';
create or replace package body test_3 as 	function exec_test return text as 	declare n text;begin 		select test_1.exec_test_2() into n;return n;end exec_test;end test_3;
create or replace function Test_proc01()return NUMERIC(100,2) as  declare  	result NUMERIC(100,2);begin 	select NUMERIC_LARGER(id,10.5) into result from Test_proc01;return result;end;language 'ploscar';
create or replace function pltest() return int as declare a int;begin		 	return a.b.c;end;
create or replace package body Calculator as   procedure produ_desc   is   begin     dbms_output.put_line(version);dbms_output.put_line(''||company);end;function divide(first int,second int)     return float   is   begin     if second=0 then       raise divzero;else       return first/second;end if;exception       when divzero then         dbms_output.put_line('  Divide zero exception');return -1;end divide;procedure display(first int,second int)   is     resu float;begin       resu:=divide(first,second);dbms_output.put_line(first::varchar(10));dbms_output.put_line('%');dbms_output.put_line(second::varchar(10));dbms_output.put_line('=');dbms_output.put_line(resu::varchar(10));end ;end Calculator;
create or replace procedure test1(i1 out int,i2 out varchar(4)) as begin 	i1:=test.i1;i2:=test.i2;end test1;
create or replace procedure p_name(column int null,j int not null default 100)   is  declare        sum int;begin 	sum = column+j;return;end;language 'ploscar';
create or replace procedure ppp  is      a int;begin     select xx into a from test_table;raise notice 'aaa';exception when others then         insert into test values(dbms_utility.format_error_backtrace);end;
create or replace procedure make_check_point() as begin   execute 'alter system checkpoint';end;
create or replace procedure p_name(when int null,j int not null default 100)   is  declare        sum int;begin 	sum = when+j;return;end;language 'ploscar';
create  or  replace  trigger   triggerddl11    before create  on lyj.schema BEGIN 	insert into lyjlyj values(2);END;
create or replace procedure p_name(is int null,j int not null default 100)   is  declare        sum int;begin 	sum = is+j;return;end;language 'ploscar';
create or replace function a()return TIMESTAMP as  declare  	result TIMESTAMP;begin 	SELECT DATE_TRUNC('HOUR',TIMESTAMP '2001-02-16 20:38:40') into result;return result;end;language 'ploscar';
create or replace procedure pro_test_min(tn varchar(10),before_count out int,after_count out int,merge_name varchar(10)) as declare 	part_name varchar(100)='';part_id int;begin 	 select PARTNAME into part_name from v_sys_tab_partitions where RELNAME='M_TEST' and PARTNAME like 'SYS_P%' and oid=(select min(oid) from v_sys_tab_partitions where RELNAME='M_TEST' and PARTNAME like 'SYS_P%' );select  count(*)  into before_count from M_TEST;execute immediate 'alter table M_TEST merge partitions '||merge_name||','||part_name ||' into partition p4';select count(*)  into after_count from M_TEST;end;language 'ploscar';
create or replace procedure PRecord(_id int)  as     DECLARE             Rdatatype1 data_type1%ROWTYPE;Rdatatype2 data_type2%ROWTYPE;Rdatatype3 data_type3%ROWTYPE;Rdatatype4 data_type4%ROWTYPE;RTboolean Tboolean%ROWTYPE  ;RTbit Tbit%ROWTYPE  ;Rdatatype11 data_type1%ROWTYPE ;Rdatatype21 data_type2%ROWTYPE ;Rdatatype31 data_type3%ROWTYPE ;Rdatatype41 data_type4%ROWTYPE ;RTboolean1 Tboolean%ROWTYPE ;RTbit1 Tbit%ROWTYPE ;begin          null;SELECT * INTO Rdatatype1  FROM data_type1 WHERE field1 = 'ling1';SELECT * INTO Rdatatype2  FROM data_type2 WHERE field1 = 'ling1';SELECT * INTO Rdatatype3  FROM data_type3 WHERE field1 = 'ling1';SELECT * INTO Rdatatype4  FROM data_type4 WHERE field1 = 'ling1';SELECT * INTO rtbit from tbit where akey=3;SELECT * INTO rtboolean from tboolean where field1=1234567;Rdatatype31.field1=Rdatatype3.field1;Rdatatype31.field2=Rdatatype3.field2;Rdatatype31.field3=Rdatatype3.field3;Rdatatype31.field4=Rdatatype3.field4;Rdatatype31.field5=Rdatatype3.field5;Rdatatype31.field6=Rdatatype3.field6;Rdatatype31.field7=Rdatatype3.field7;Rdatatype31.field8=Rdatatype3.field8;Rdatatype31.field9=Rdatatype3.field9;Rdatatype31.field10=Rdatatype3.field10;Rdatatype31.field11=Rdatatype3.field11;Rdatatype31.field12=Rdatatype3.field12;Rdatatype31.field13=Rdatatype3.field13;return ;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE COL_TYP IS VARRAY(20) OF INT;MyTab COL_TYP := COL_TYP(1,2,3,4,5);K INT;BEGIN 	MyTab(2) := NULL;DBMS_OUTPUT.PUT_LINE('NEXT IS : '||MYTAB.NEXT(1));DBMS_OUTPUT.PUT_LINE('NEXT IS : '||MYTAB.NEXT(-100));DBMS_OUTPUT.PUT_LINE('NEXT IS : '||MYTAB.NEXT(10));K	:= MYTAB.FIRST;WHILE K IS NOT NULL LOOP 		DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||MYTAB(K));K	:= MYTAB.NEXT(K);END LOOP;END;
create or replace procedure if(a in int )  as begin       null;end;language 'ploscar';
create or replace function a()return TIMESTAMP as  declare  	result TIMESTAMP;begin 	SELECT DATE_TRUNC('MILLENNIUM','2001-02-16 20:38:40') into result;return result;end;language 'ploscar';
create or replace function a(id time)return bool as  declare  	result bool;begin 	select OVERLAPS(id,'12:12:15','12:12:16','12:12:17') into result;return result;end;language 'ploscar';
create or replace procedure dump_tree ( 	index_name varchar(1000) ) is 	index_oid number;begin 	select oid into index_oid from sys_Class where relname = index_name;execute immediate ('alter system set events (immediate trace name treedump level ' || index_oid || ')');end dump_tree;
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE COL_TYP IS TABLE OF INT INDEX BY BINARY_INTEGER;MyTab COL_TYP;K INT;BEGIN 	FOR I IN -10..10 LOOP 	    MyTab(I) := I;END LOOP;DBMS_OUTPUT.PUT_LINE('Prior is : '||MyTab.PRIOR(3));DBMS_OUTPUT.PUT_LINE('Prior is : '||MyTab.PRIOR(100));DBMS_OUTPUT.PUT_LINE('Prior is : '||MyTab.PRIOR(-33));END;
create or replace procedure loop_while ( 	start_value in number,  	end_value in number ) is 	current_value number := start_value;begin 	while current_value <= end_value 	loop 				 		delete from td_12735 where id=11 or id=12;begin 			insert into td_12735 values(11, rpad('A', 121, 'A'));insert into td_12735 values(12, rpad('B', 121, 'B'));commit;end;delete from td_12735 where id=11 or id=12;begin 			insert into td_12735 values(12, rpad('B', 121, 'B'));insert into td_12735 values(11, rpad('A', 121, 'A'));commit;end;current_value := current_value+1;end loop;end loop_while;
CREATE TRIGGER tri2 after insert     on bbbb for each row  begin    	insert into cccc values (:new.i);insert into cccc values (100/:new.i);end;
create trigger a_column24 AFTER update ON a FOR EACH ROW  Begin 	RAISE NOTICE 'ordinary_trigger() called: action = %, when = %, level = %', TG_OP, TG_WHEN, TG_LEVEL;insert into result values(1, TG_OP, TG_WHEN, TG_LEVEL);end;
create or replace procedure p(c out refcursor) is begin     open c for execute 'select * from  test1 order by a';end;
CREATE OR REPLACE PROCEDURE p_1 IS       sql_id INTEGER;sql_stmt VARCHAR2(1000);BEGIN     sql_id := DBMS_SQL.OPEN_CURSOR();sql_stmt := 'begin insert into test values(22);insert into test values(33); end;';DBMS_SQL.PARSE(sql_id, sql_stmt, DBMS_SQL.NATIVE);equal_int(0, DBMS_SQL.EXECUTE(sql_id));DBMS_SQL.CLOSE_CURSOR(sql_id);END;
create or replace procedure Defined_type_record_3() as  declare c1 refcursor;v1 tb_test.a%TYPE;TYPE d_type_1 IS RECORD (    f1 v1%TYPE,    f2 tb_test.b%TYPE );var_1 d_type_1;begin open c1 for select * from tb_test;fetch c1 into var_1 ;if c1%found then     raise notice '%   %',var_1.f1,var_1.f2;end if;end;
CREATE TRIGGER tri2 after insert     on test for each row execute procedure tri2('aaa','bbb');insert into test values(1,2);
create or replace function Test_proc01(id real)return float8 as  declare  	result float4;begin 	select FLOAT4LARGER(id,1233) into result;return result;end;language 'ploscar';
create or replace trigger tg1 after insert on test for each row begin     insert into test1 values (wcmTest(10));end;
CREATE OR REPLACE PROCEDURE p_1 IS       sql_id INTEGER;sql_stmt VARCHAR2(1000);BEGIN     sql_id := DBMS_SQL.OPEN_CURSOR();sql_stmt := 'WRONG STMT!';DBMS_SQL.PARSE(sql_id, sql_stmt, DBMS_SQL.NATIVE);equal_int(0, DBMS_SQL.EXECUTE(sql_id));PERFORM RAISE_APPLICATION_ERROR(-20010, '���󣺲�Ӧ��ִ�е���һ�����');EXCEPTION     WHEN OTHERS THEN         DBMS_SQL.CLOSE_CURSOR(sql_id);END;
create or replace procedure a()  as  declare  	result text;begin 	select SET_CONFIG('EFFECTIVE_CACHE_SIZE',d,TRUE) into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace function Test_proc01(id NUMERIC(100,2))return NUMERIC(100,2) as  declare  	result NUMERIC(100,2);begin 	select NUMERIC_POWER(id,10.5) into result;return result;end;language 'ploscar';
create or replace procedure a()  as  declare  	result varchar(10);begin 	select LPAD('abcd',10,'?*') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure a()  as  declare  	result text;begin 	select QUOTE_IDENT(c) into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure dyn_exec(a1 int) as v1 int := 1;v2 varchar(20) := 'shangbo';v3 date := '2010-03-23';string varchar(20);begin 	execute immediate 'insert into mytablemytable values(:1,:2,:3) returning b into :retval' 	using in v1, out v2, out v3,out string;raise notice '% % % return %',v1,v2,v3,string;end;
create or replace function Test_proc01()return int2 as  declare  	result int2;begin 	select INT2SMALLER(-22.3,-10.5) into result;return result;end;language 'ploscar';
create or replace procedure exesql2(a inout int)  as begin      update Type_800_tab2 set column1=-3.4e+38,column11=3.4e+38,column21='�Ѿ�����update', column31=3.14159+sin(1.5),column32=3.14159,column33=3.14159, column51=3.14159+sin(1.5),column52=-1.79e+308,column53=1.79e+308, column71='����upd',column72='�Ѿ�upd', column91=12345678,column92=-2147483648,column93=2147483647, column101=CURRENT_DATE,column131='23:59:59',column151=CURRENT_TIMESTAMP, column171=-9223372036854775808+12345678,column172=-9223372036854775808,column173=9223372036854775807, column191=-32768,column192=32767,column193=12345, column211=-129,column212=127,column213=123   where Type_800_tab2.column0 between 9123624 and 9993485;return ;end;language 'ploscar';
create or replace package b as 	procedure p2(r1 out int) ;END;
create or replace function Test_proc01()return int8 as  declare  	result int8;begin 	select factorial(5::int8) into result;return result;end;language 'ploscar';
create or replace function Test_proc01()return float8 as  declare  	result float8;id float8:=25.56;begin 	select EXP(id) into result;return result;end;language 'ploscar';
CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS   TYPE vc_array IS TABLE OF VARCHAR2(200);TYPE numlist IS TABLE OF NUMBER;PROCEDURE do_query_1 (                         placeholder vc_array,                         bindvars vc_array,                         sql_stmt VARCHAR2                        );END My_Types;
create or replace package test  as 	function t1 return int;end test;
CREATE OR REPLACE function pltest() returns int as begin 	while loop 	return 0;end loop;return 1;end;
create or replace procedure a(id in char(10),b in varchar(10))  as  declare  	result bool;begin 	select TEXTICNLIKE(id,b) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure a()  as  declare  	result varchar(10);id char(10):='abchelloab';b varchar(10):='c';begin 	select RTRIM(id,b) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure p_name(close int null,j int not null default 100)   is  declare        sum int;begin 	sum = close+j;return;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE COLLECTION_TEST() IS   DECLARE 	TYPE COL_TYP IS VARRAY(20) OF VARCHAR2(10);MyTab COL_TYP := COL_TYP('A','B','C') ;BEGIN 	DBMS_OUTPUT.PUT_LINE('SUCCESS!');END;
create or replace function p_Text(_id int,str varchar(22)) return varchar(70) as declare      aint1 constant int := 2147483647;atinyint1 constant tinyint := 127;abigint1 constant bigint := -9223372036854775808;asmallint1 constant smallint  :=-2^15;asmallint2 constant smallint  :=2^15-1;afloat1 float := 3.14159;afloat2 float not null default 3.4e+38;afloat3 float not null default -3.4e+38;adoubleprecision1 constant double precision :=  1.79e+308 ;adoubleprecision2 constant double precision := -1.79e+308 ;areal constant real := 3.4e+38;areal1 constant real := -3.4e+38;decimal1 constant decimal(6,0)   := 666666;numericl1 constant numeric(6,0)  := 666666;aint01  int := 2147483647;atinyint01  tinyint := 127;abigint01  bigint := -9223372036854775808;asmallint01  smallint  :=-2^15;asmallint02  smallint  :=2^15-1;afloat01 float := 3.14159;afloat02 float not null default 3.4e+38;afloat03 float not null default -3.4e+38;adoubleprecision01  double precision :=  1.79e+308 ;adoubleprecision02  double precision := -1.79e+308 ;area0l  real := 3.4e+38;area0l1 real := -3.4e+38;decima0l1  decimal(6,0)   := 666666;numeric0l1 numeric(6,0)  := 666666;M INT;begin        m = 2;case _id          when 1 then          return aint01 ;when 2 then          return aint1-m;when 3 then          return atinyint01;when 4 then          return asmallint02;when 5 then          return decimal1;when 6 then           return numeric0l1+9;when 7 then         return numeric0l1*9+2^5+128/5;when 8 then return  p_exeText(1);when 9 then return  p_exeText(2);when 10 then return  p_exeText(3);else          return p_exeNumberInt(1)-2;end case;end;language 'ploscar';
create or replace function num_param(x int) return int as	 begin   return x + 100;end;language 'ploscar';
create procedure sm_1.p_20()   as  begin 	raise notice 'This is a only test';end;language 'ploscar';
create or replace procedure a()  as  declare  	result text;id bigint:=180;begin 	select TO_HEX(id) into result;dbms_output.put_line(result);end;language 'ploscar';
CREATE OR REPLACE function pltest() returns int as declare aaa student%rowtype;begin         <<zhangsan>>for aaa in select * from student loop 	raise notice '%',aaa.a;end loop;return 1;end;
create or replace procedure e1() as begin 	raise notice 'this is a extern procedure E1';e2();end;
create   or   replace   procedure   pro ( 	id in number, 	r_salary  number, 	r_action  varchar2(20))  as  declare 	sql_str varchar2(500);begin 	sql_str:='alter table :1 add column (addCol number)' ;execute immediate sql_str using r_action;end;
create or replace function pltest() return int as rec1 pltest.s%type;begin		 	return 1;end;
create or replace procedure a()  as  declare  	result text;begin 	select TEXT_LARGER('abchello',NULL) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure clean() as declare   cur		refcursor;tabname	varchar(256);username varchar(256);seqname varchar(256);proname varchar(256);begin   raise notice 'all users will be deleted, cannot recovery';open cur for select* from v_sys_user where usesysid not in(1,2,3,4,33165,33167,33169);loop 	fetch cur into username;exit when NOT FOUND;raise notice 'drop user: %', username;execute 'drop user ' || username || ' cascade';end loop;close cur;raise notice 'all tables will be deleted, cannot recovery';open cur for select table_name from tables where table_type='BASE TABLE';loop 	fetch cur into tabname;exit when NOT FOUND;raise notice 'deleting table: %', tabname;execute 'drop table ' || tabname || ' cascade';end loop;close cur;raise notice 'all sequences will be deleted, cannot recovery';open cur for select distinct seq_name from v_sys_sequence_privileges;loop 	fetch cur into seqname;exit when NOT FOUND;raise notice 'deleting sequence: %', seqname;execute 'drop sequence ' || seqname || ' cascade';end loop;close cur;raise notice 'all procedures will be deleted, cannot recovery';open cur for select procedure_name from v_sys_procedure where procedure_schem='SYSDBA' and procedure_name not like 'CLEAN';loop 	fetch cur into proname;exit when NOT FOUND;raise notice 'deleting procedure: %', proname;execute 'drop procedure ' || proname || ' cascade';end loop;close cur;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE p_1 IS       sql_id INTEGER;sql_stmt VARCHAR2(1000);c1_val INTEGER;cursor cur_test is select c1 from test;BEGIN     sql_id := DBMS_SQL.OPEN_CURSOR();open cur_test;loop         fetch cur_test into c1_val;exit when cur_test%notfound;sql_stmt := 'insert into test2 values(22, null)';DBMS_SQL.PARSE(sql_id, sql_stmt, DBMS_SQL.NATIVE);equal_int(1, DBMS_SQL.EXECUTE(sql_id));end loop;DBMS_SQL.CLOSE_CURSOR(sql_id);END;
create trigger a_column26 BEFORE update of id ON a FOR EACH ROW  Begin 	RAISE NOTICE 'column_trigger() called: action = %, when = %, level = %', TG_OP, TG_WHEN, TG_LEVEL;insert into result values(0, TG_OP, TG_WHEN, TG_LEVEL);end;
create or replace procedure test_pl()  is declare e2 exception;begin     declare     e1 exception;pragma exception_init(e1,-20666);begin     raise e1;end;exception when e1 then raise notice 'nihao';return;end;language 'ploscar';
CREATE or replace function p_goto() RETURN setof record AS  DECLARE    temprec main_table%rowtype;foo INTEGER := 4;BEGIN  	goto wangqi;if foo > 0 then 	  RAISE NOTICE 'foo IS %',foo;end if;<<wangqi>> 	select * into temprec from main_table limit 1;return next temprec;return;END;LANGUAGE 'ploscar';
create or replace procedure a()  as  declare  	result TEXT;id char(10):='abchelloab';b text:=null;begin 	select TEXTICLIKE(id,b) into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace package body test as 	procedure t1(i1 out text)  as 	begin 		i1:='null';end;procedure t1(i1 out int)  as 	begin 		i1:=100;end;function t1(i1 int ,i2 text) return int as 	begin 		return i1+i2::int;end;function t1(i1 text ,i2 int) return text as 	begin 		return i1||i2;end;end test;
create or replace package table as end table;
create or replace procedure a()  as  declare  	result varchar(20);begin 	select CONCAT('hello, ', 'world') into result;dbms_output.put_line(result);end;language 'ploscar';
CREATE TRIGGER t2_trigger1 BEFORE INSERT OR UPDATE ON t1 FOR EACH ROW  BEGIN     update t2 set c1=100;END;
create or replace procedure forexample(a inout int)   as declare i char(10);j varchar(700) not null default '123456789';k int not null  :=-2147483648;begin   for i in 1..100 loop     for j in 1..50 loop       for k in 1..5 loop         a:=a+1;end loop;end loop;a:=a+1;end loop;return ;end;language 'ploscar';
create or replace procedure forexample(a inout int)   as declare i int;k int;begin   for i in reverse  100..1 loop     for k in reverse  100..20 loop       for k in reverse  100..30 loop         a:=a+1;end loop;end loop;a:=a+1;end loop;return ;end;language 'ploscar';
CREATE OR REPLACE PROCEDURE TEST() AS  declare ss int;BEGIN update t1 set i = 1112 where i = 111 RETURNING * into ss;raise notice 'nihao %',ss;update t1 set  i = 2222 where i = 222 RETURNING * into ss;raise notice 'nihao %',ss;update t1 set  i = 3332 where i = 11331 RETURNING * into ss;raise notice 'nihao %',ss;return;END;language 'ploscar';
create or replace procedure a()  as  declare  	result text;begin 	select INSERT_TEXT (c,8,4,'another')  into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace package test as    aa int;end;
create or replace procedure a()  as  declare  	result bytea;begin 	select decoding(D, 'BASE64') into result from a;dbms_output.put_line(result);end;language 'ploscar';
create or replace procedure test() as  curs1 REFCURSOR;curs2 REFCURSOR;emp_rec employees%ROWTYPE;BEGIN    OPEN curs1 FOR SELECT * FROM employees WHERE employee_id < 120;if  curs2%isopen then     DBMS_OUTPUT.PUT_LINE('curs1 isopen!(true)');end if;CLOSE curs1;if  curs2%isopen then     DBMS_OUTPUT.PUT_LINE('curs1 isopen!(false)');end if;END;
create or replace procedure a()  as  declare  	result text;begin 	select CONCAT('hello, ', 'world') into result;dbms_output.put_line(result);end;language 'ploscar';
create or replace package body test as	 	 	i1 int:=44;i2 varchar(4)='a4';PRAGMA EXCEPTION_INIT(var3,-20666);procedure var4(r1 out text) as 	begin 		r1:=var2;end var4;function var5(i1 int,i2 int) return int as 	begin 		return i1+i2;end var5;procedure var6(r1 out text) as 	begin 		perform RAISE_APPLICATION_ERROR(-20666,'�쳣������Ϣ.');exception when var3 then 			r1:='�쳣������Ϣ.';end var6;begin 		var2:='ɳ����';end test;
CREATE or replace function p_goto() RETURN INTEGER AS  DECLARE    foo INTEGER := 4;BEGIN  	goto wangqi;if foo > 0 then 	  foo = foo - 1;end if;BEGIN   <<wangqi>> 		if foo > 0 then RAISE NOTICE 'foo IS %',foo;end if;END;return 0;END;LANGUAGE 'ploscar';
create or replace procedure scope(localVar out text,privateVar out text ,r1 out text,r2 out text) is 	local_var int default 7;private_var int:=50;begin  	 	localVar:='local_var:   '||local_var;local_var:=private_var*local_var;privateVar:='local_var:=private_var*local_var:'||local_var;private_var:=local_var;r2:='private_var:=local_var:'||private_var;r1:='private_var: '||private_var;end;
create or replace function Test_proc01()return float8 as  declare  	result float8;begin 	select CBRT(25.56) into result;return result;end;language 'ploscar';
create or replace function a()return int as  declare  	result int;begin 	select DAYS(id) into result from a;return result;end;language 'ploscar';
create or replace procedure forall_5()   as declare v_varray int[] := '{10,20,30,40,50,60}';begin forall idx in 1..6 select elem into v_varray[idx]  from tb_forall;return;end;language 'ploscar';
CREATE OR REPLACE FUNCTION GET_FIRST_USER2() RETURNS USERS  AS     B T1%ROWTYPE;BEGIN     SELECT * INTO B FROM T1 ORDER BY USERID LIMIT 1;RETURN B;END;LANGUAGE 'PLOSCAR';
